import fs from 'fs';
import Database from 'better-sqlite3';
import {BaseService} from '../core/BaseService';
import {TransactionUtils} from '../common';
import {DataTransferFormat, PartialWithType} from "../types";

/**
 * 数据传输工具
 */
export class DataTransferUtil {
  /**
   * 导出表数据到JSON文件
   * @param service 数据服务
   * @param filePath 导出文件路径
   * @param options 导出选项
   * @returns 导出的数据条数
   */
  public static exportToJson<T extends object>(
    service: BaseService<T>,
    filePath: string,
    options?: {
      where?: PartialWithType<T>;
      orderBy?: string;
      metadata?: Record<string, any>;
    }
  ): number {
    try {
      // 使用服务查询数据
      const records = service.find({
        where: options?.where,
        orderBy: options?.orderBy
      });

      // 准备导出数据
      const exportData: DataTransferFormat = {
        tableName: (service as any).tableInfo.name,
        records,
        metadata: {
          exportTime: Date.now(),
          ...options?.metadata
        }
      };

      // 写入文件
      fs.writeFileSync(filePath, JSON.stringify(exportData, null, 2));

      return records.length;
    } catch (error) {
      console.error('导出数据失败:', error);
      return 0;
    }
  }

  /**
   * 从JSON文件导入数据到表
   * @param service 数据服务
   * @param filePath 导入文件路径
   * @param options 导入选项
   * @returns 导入的数据条数
   */
  public static importFromJson<T extends object>(
    service: BaseService<T>,
    filePath: string,
    options?: {
      /**
       * 导入前清空表
       */
      clearTable?: boolean;
      /**
       * 检查表名是否匹配
       */
      checkTableName?: boolean;
      /**
       * 数据转换函数
       */
      transform?: (record: Record<string, any>) => Partial<T>;
    }
  ): number {
    try {
      // 检查文件是否存在
      if (!fs.existsSync(filePath)) {
        throw new Error(`导入文件不存在: ${filePath}`);
      }

      // 读取文件内容
      const fileContent = fs.readFileSync(filePath, 'utf8');
      const importData = JSON.parse(fileContent) as DataTransferFormat;

      // 检查表名是否匹配
      if (options?.checkTableName &&
        importData.tableName !== (service as any).tableInfo.name) {
        throw new Error(
          `表名不匹配: 期望 ${(service as any).tableInfo.name}, 实际 ${importData.tableName}`
        );
      }

      // 清空表
      if (options?.clearTable) {
        const db = (service as any).db as Database.Database;
        db.exec(`DELETE FROM ${(service as any).tableInfo.name}`);
      }

      // 准备导入数据
      let records = importData.records as Record<string, any>[];

      // 应用数据转换
      if (options?.transform) {
        records = records.map(options.transform);
      }

      // 使用事务批量插入
      service.batchInsert(records as Partial<T>[]);

      return importData.records.length;
    } catch (error) {
      console.error('导入数据失败:', error);
      return 0;
    }
  }

  /**
   * 导出查询结果到CSV文件
   * @param db 数据库实例
   * @param query SQL查询语句
   * @param filePath 导出文件路径
   * @param params 查询参数
   * @returns 导出的数据条数
   */
  public static exportQueryToCsv(
    db: Database.Database,
    query: string,
    filePath: string,
    params: any[] = []
  ): number {
    try {
      // 执行查询
      const stmt = db.prepare(query);
      const records = stmt.all(...params) as Record<string, any>[];

      if (records.length === 0) {
        // 输出空文件
        fs.writeFileSync(filePath, '');
        return 0;
      }

      // 提取列名
      const headers = Object.keys(records[0]);

      // 生成CSV内容
      let csvContent = headers.join(',') + '\n';

      for (const record of records) {
        const row = headers.map(header => {
          const value = record[header];
          // 处理CSV中特殊字符
          if (value === null || value === undefined) {
            return '';
          }
          if (typeof value === 'string') {
            // 如果包含逗号、引号或换行符，用引号包围并将引号替换为两个引号
            if (value.includes(',') || value.includes('"') || value.includes('\n')) {
              return `"${value.replace(/"/g, '""')}"`;
            }
            return value;
          }
          return String(value);
        }).join(',');

        csvContent += row + '\n';
      }

      // 写入文件
      fs.writeFileSync(filePath, csvContent);

      return records.length;
    } catch (error) {
      console.error('导出查询结果失败:', error);
      return 0;
    }
  }

  /**
   * 从一个数据库复制表数据到另一个数据库
   * @param sourceDb 源数据库实例
   * @param targetDb 目标数据库实例
   * @param tableName 表名
   * @param options 复制选项
   * @returns 复制的数据条数
   */
  public static copyTableData(
    sourceDb: Database.Database,
    targetDb: Database.Database,
    tableName: string,
    options?: {
      where?: string;
      params?: any[];
      batchSize?: number;
    }
  ): number {
    try {
      // 获取表结构信息
      const sourceColumns = sourceDb.prepare(
        `PRAGMA table_info(${tableName})`
      ).all() as Array<{ name: string }>;

      const targetColumns = targetDb.prepare(
        `PRAGMA table_info(${tableName})`
      ).all() as Array<{ name: string }>;

      // 找出共同的列
      const sourceColNames = sourceColumns.map(c => c.name);
      const targetColNames = targetColumns.map(c => c.name);

      const commonColumns = sourceColNames.filter(col => targetColNames.includes(col));

      if (commonColumns.length === 0) {
        throw new Error(`表 ${tableName} 在源数据库和目标数据库之间没有共同列`);
      }

      // 执行复制
      const columnsStr = commonColumns.join(', ');
      const placeholders = commonColumns.map(() => '?').join(', ');

      // 准备查询
      const whereClause = options?.where ? ` WHERE ${options.where}` : '';
      const selectSql = `SELECT ${columnsStr} FROM ${tableName}${whereClause}`;
      const insertSql = `INSERT INTO ${tableName} (${columnsStr}) VALUES (${placeholders})`;

      // 获取源数据
      const sourceRows = sourceDb.prepare(selectSql).all(...(options?.params || [])) as Record<string, any>[];

      // 没有数据可复制
      if (sourceRows.length === 0) {
        return 0;
      }

      // 使用事务批量插入
      TransactionUtils.executeTransaction(targetDb, () => {
        const insertStmt = targetDb.prepare(insertSql);

        for (const row of sourceRows) {
          const values = commonColumns.map(col => row[col]);
          insertStmt.run(...values);
        }
      });

      return sourceRows.length;
    } catch (error) {
      console.error(`复制表 ${tableName} 数据失败:`, error);
      return 0;
    }
  }
}
